from logging.handlers import RotatingFileHandler
from flask import Flask, session, render_template
import pymysql
import logging
from views.login import lg
from views.index import ix
from views.personal import ps
from views.management import mg
from views.province import pc
from datetime import timedelta
import os
from config import Config
# from model.china_job import China_job
# from flask_sqlalchemy import SQLAlchemy
# from sqlalchemy import *
# db = SQLAlchemy()
# db.init_app(app)
# 1
app = Flask(__name__)
app.register_blueprint(lg)
app.register_blueprint(ix)
app.register_blueprint(mg)
app.register_blueprint(ps)
app.register_blueprint(pc)

app.config.update(
    # SQLALCHEMY_DATABASE_URI='mysql+pymysql://ye_data:Ye7187jq@ye-data0.mysql.rds.aliyuncs.com:6666/job_data?charset=utf8',
    # SQLALCHEMY_TRACK_MODIFICATIONS=False,
    # SQLALCHEMY_POOL_TIMEOUT=60,
    # SQLALCHEMY_POOL_RECYCLE=10,
    # SQLALCHEMY_POOL_SIZE=100,
    SECRET_KEY=os.urandom(24),
    PERMANENT_SESSION_LIFETIME=timedelta(hours=1),
)


# 设置日志等级
logging.basicConfig(level=logging.DEBUG)
# 创建一个日志记录者，指出日志保存的地址，然后日志文件的大小最大多少
file_log_handler = RotatingFileHandler('logs/log', maxBytes=1024*1024, backupCount=10)
# 设置日志格式
formatter=logging.Formatter('%(levelname)s %(filename)s %(lineno)d %(message)s')
# 设置应用日志格式的日志记录者
file_log_handler.setFormatter(formatter)
# 将日志书写者添加到全局日志工具中
logging.getLogger().addHandler(file_log_handler)


# 重定向
@app.route('/index')
def index():
    connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD,
                              port=Config.MYSQL_PORT, database=Config.MYSQL_DATABASE)
    cursor = connect.cursor()
    if session.get('power') == None:
        session['power'] = -404
    # 地图和柱状图
    # rows1 = db.session.query(China_job.province, func.avg(func.SUBSTRING_INDEX(China_job.salary, "-", 1)
    #                                                      +func.SUBSTRING_INDEX(China_job.salary, "-", -1))/2)\
    #     .filter(China_job.salary != "面议").group_by(China_job.province).all()
    cursor.execute('select province, avg((substring_index(salary, "-", 1)+ substring_index(salary, "-", -1))/2)'
                   ' as a from china_job where salary != "面议" group by province order by a')
    rows1 = cursor.fetchall()
    map_data = []
    axis = []
    data = []
    values = list(map(lambda x: x[1], list(rows1)))
    map_max = int(max(values))
    map_min = int(min(values))
    for row in rows1:
        axis.append(row[0])
        data.append(round(row[1], 2))
        map_data.append({'name': row[0], 'value': round(row[1], 2)})
    # 饼图
    # rows2 = db.session.query(China_job.position, func.count(China_job.province).label("c"))\
    #     .group_by(China_job.position).order_by(desc("c")).all()[:10]
    cursor.execute('select position,count(*) as c from china_job group by position order by c desc limit 10')
    rows2 = cursor.fetchall()
    pie_data1 = []
    pie_legend1 = []
    for row in rows2:
        pie_legend1.append(row[0])
        pie_data1.append({'name': row[0], 'value': row[1]})
    # rows3 = db.session.query(China_job.education, func.count(China_job.province)).group_by(China_job.education).all()
    cursor.execute('select education, count(province) from china_job group by education')
    rows3 = cursor.fetchall()
    pie_data2 = []
    pie_legend2 = []
    for row in rows3:
        pie_legend2.append(row[0])
        pie_data2.append({'name': row[0], 'value': row[1]})
    title1 = 'TOP10工作类型占比'
    title2 = '学历类型占比'

    return render_template('index.html', map_data=map_data, map_max=map_max,
                           map_min=map_min, axis=axis, data=data,
                           pie_legend1=pie_legend1, pie_legend2=pie_legend2,
                           pie_data1=pie_data1, pie_data2=pie_data2,
                           title1=title1, title2=title2
                           )

if __name__ == '__main__':
    app.run(port=2222, debug=True)